package org.gosdt.ew.db;

import java.io.BufferedInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


/**
 * class for connecting to DB, singleton
 * @author anton
 */
public class EWDataBase {
	private static final String CREATE_TABLES_SQL = "/org/gosdt/ew/resurse/createTables.sql";
	private static EWDataBase instance;
	private String dbURL = "jdbc:derby:EW;create=true";
	private Connection conn = null;

	private EWDataBase() {
		init();
	}

	public static synchronized EWDataBase getInstance() {
		if (instance == null) {
			instance = new EWDataBase();
		}
		return instance;
	}

	private void init() {
		try {
			Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
			conn = DriverManager.getConnection(dbURL);
			if (!isTablesExists())
				createTables(getTablesSQLScript());

		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	private void createTables(String sqlScript) throws SQLException {
		Statement stmt = conn.createStatement();
		String[] query = sqlScript.split("\n\n");

		for (int i = 0; i < query.length; i++) {
			stmt.executeUpdate(query[i]);
		}

		stmt.close();
	}

	private String getTablesSQLScript() throws IOException {
		return readFileAsString(getTablesSQLStream());
	}

	private InputStream getTablesSQLStream() {
		return getClass().getResourceAsStream(CREATE_TABLES_SQL);
	}

	private boolean isTablesExists() throws SQLException {
		ResultSet tables = getAllTables();

		while (tables.next()) {
			if (isProfileTable(tables))
				return true;
		}

		return false;
	}

	private boolean isProfileTable(ResultSet tables) throws SQLException {
		return tables.getString("TABLE_NAME").toUpperCase().equals("PROFILES");
	}

	private ResultSet getAllTables() throws SQLException {
		String[] tableTypes = { "TABLE" };
		return conn.getMetaData().getTables(null, null, null, tableTypes);
	}

	private String readFileAsString(InputStream filePath) throws IOException {
		byte[] buffer = new byte[(int) filePath.available()];
		BufferedInputStream fis = new BufferedInputStream(filePath);
		
		fis.read(buffer);
		fis.close();

		return new String(buffer);
	}

	public Connection getConn() {
		return conn;
	}

}
